﻿using System;
using System.Collections.Generic;

using System.Text;
using Estimate.Common.Entity;
using System.Data.SqlClient;
using Estimate.Common.DBFieldTypes;

namespace Estimate.Common.Repository
{
    /// <summary>
    /// 雨水实况表，实现了 RainReal 的数据库操作
    /// </summary>
    public class RainRealRepository :BaseRepository
    {       
        /// <summary>
        /// 初始化一张实况表数据库访问类
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="tableName">表名</param>
        public RainRealRepository(string connectionString, string tableName) : base(connectionString,tableName)
        {
        }

        public override string GetCreateTableSql()
        {
            string sqlcreate = String.Format("CREATE TABLE [{0}](id bigint identity(1,1), [date] char(8) not null, type varchar(8) not null, [value] text) \n", _tableName);
            sqlcreate += String.Format("CREATE CLUSTERED INDEX {0}_INDEX ON {0} (date,type) \n", _tableName, _tableName);
            sqlcreate += String.Format("alter table {0} add primary key (id)", _tableName);
            return sqlcreate;
        }

        /// <summary>
        /// 给定日期和类型，查出值
        /// </summary>
        /// <param name="date">日期</param>
        /// <param name="type">类型</param>
        /// <returns></returns>
        public string GetValue(DateTime date, RealRainValueType type)
        {
            string sql = String.Format("SELECT value FROM [{0}] WHERE date=@date and type=@type", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@date", date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@type", type.ToString());

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection,sql, param);

            if (reader.Read())
            {
                string result = reader["value"].ToString();
                connection.Close();
                return result;
            }

            return "";
        }

        /// <summary>
        /// 给定日期，获取当天的实况
        /// </summary>
        /// <param name="date">日期</param>
        /// <returns>实况数据数组</returns>
        public RainRealEntity[] GetEntitiesWithDate(DateTime date)
        {
            List<RainRealEntity> result = new List<RainRealEntity>();
            string sql = String.Format("SELECT value,type FROM [{0}] WHERE date=@date", _tableName);
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@date", date.Date.ToString("yyyyMMdd"));

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);

            while(reader.Read())
            {
                string value = reader["value"].ToString();
                RealRainValueType type = (RealRainValueType)Enum.Parse( typeof(RealRainValueType), reader["type"].ToString());
                RainRealEntity entity = new RainRealEntity(date, type, value);
                result.Add(entity);
            }
            connection.Close();
            return result.ToArray();
        }

        public RainRealEntity[] GetEntitiesWithDate(DateTime beginDate,DateTime endDate)
        {
            List<RainRealEntity> result = new List<RainRealEntity>();
            string sql = String.Format("SELECT * FROM [{0}] WHERE date>=@begindate and date<=@enddate", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@begindate", beginDate.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@enddate", endDate.Date.ToString("yyyyMMdd"));

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);

            while (reader.Read())
            {
                DateTime date = DateTime.ParseExact(reader["date"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                RealRainValueType type = (RealRainValueType)Enum.Parse(typeof(RealRainValueType), reader["type"].ToString());
                RainRealEntity entity = new RainRealEntity(date, type, reader["value"].ToString());
                result.Add(entity);
            }
            connection.Close();
            return result.ToArray();
        }

        /// <summary>
        /// 记录是否存在
        /// 根据 date 和 type 字段查询
        /// </summary>
        /// <returns>查询结果</returns>
        public bool IsRecordExist(RainRealEntity entity)
        {
            string sql = String.Format("SELECT * FROM [{0}] WHERE date=@date and type=@type", _tableName);
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@date", entity.Date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@type", entity.Type.ToString());
            return DBHelper.Exists(_connectionString, sql, param);
        }

        /// <summary>
        /// 添加一条新记录
        /// </summary>
        public void InsertRecord(RainRealEntity entity)
        {
            string sql = String.Format("INSERT INTO [{0}] (date,type,value) values(@date,@type ,@value)", _tableName);
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@date", entity.Date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@type", entity.Type.ToString());
            param[2] = new SqlParameter("@value", entity.Value.ToString());
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        /// <summary>
        /// 更新一条记录的值
        /// </summary>
        public void UpdateRecordValue(RainRealEntity entity)
        {
            string sql = String.Format("UPDATE [{0}] SET value=@value WHERE date=@date and type=@type ", _tableName);
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@date", entity.Date.Date.ToString("yyyyMMdd"));
            param[1] = new SqlParameter("@type", entity.Type.ToString());
            param[2] = new SqlParameter("@value", entity.Value.ToString());
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        /// <summary>
        /// 设置一条记录的值。如果不存在则会创建一条新记录，否则会更新
        /// </summary>
        public void SetValue(RainRealEntity entity)
        {
            if (this.IsRecordExist(entity))
                this.UpdateRecordValue(entity);
            else
                this.InsertRecord(entity);
                      
        }
    }
}
